﻿using System;
using System.Collections.Generic;
using System.Data;
using HighDiveEngine.Aggregates;
using HighDiveEngine.IoEngines;
using HighDiveEngine.Model;
using HighDiveEngine.Resources;
using log4net;
using System.Globalization;

namespace HighDiveAdmin.Handlers
{
    /// <summary>
    /// Dbhandler acts as a facade for the SQLite database used for permanent storage
    /// It facilitates methods to simplify handling the database connection and to avoid having to use SQL in the other classes
    /// For example, us DbHandler.Instance.SaveObject(object) to save an object to the database.
    /// </summary>
    public class DbHandler
    {
        private static readonly ILog Log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        #region MemberData&Properties
        private static readonly DbHandler ThisInstance = new DbHandler();

        /// <summary>
        /// The singleton-instance of the DbHandler
        /// </summary>
        public static DbHandler Instance { get { return ThisInstance; } }

        public DbEngine DbEngine { get; private set; }

        #endregion

        #region ConstructorAndhTableInitialization
        private DbHandler()
        {
            DbEngine = new DbEngine();
            DbEngine.DbLocation = "Files/HighDive.db3";
        }

        #endregion

        #region PublicMethods

        public Dive LoadDiveFromDb(int? id)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Loading dive with id: " + id); }

            var diveRow = DbEngine.SqlQuery("SELECT * FROM Dives WHERE Id = " + id + ";").Rows[0];

            var dict = new Dictionary<int, Diver>();
            var diver = _getReferencedDiver(dict, Convert.ToInt32(diveRow["Diver"]));

            return new Dive
            {
                DbId = Convert.ToInt32(diveRow["Id"]),
                Diver = diver,
                DiveCode = (string)diveRow["DiveCode"],
                Difficulty = Convert.ToDouble(diveRow["Difficulty"]),
                DiveNumber = Convert.ToInt32(diveRow["DiveNumber"]),
                DiveHeight = Convert.ToInt32(diveRow["DiveHeight"]),
                QueueOrderNumber = Convert.ToInt32(diveRow["QueueOrder"]),
                DiveScoreList = new DiveScoreList(),
                Date = (string)diveRow["Date"]
            };
        }

        public void SetContestStatus(ContestStatus status, int? contestId)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Setting status to " + status + " for contest with id: " + contestId); }
            DbEngine.SqlCommand("UPDATE Contests SET Status = '" + status + "' WHERE Id = " + contestId + ";");
        }

        /// <summary>
        /// Resets a contest by nulling all judgescores and setting it's status to unstarted
        /// </summary>
        /// <param name="contestId">Id of the contest that should be reset</param>
        /// <returns>The resetted contest</returns>
        public Contest ResetContest(int? contestId)//Contest contest)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Resetting contest with id: " + contestId); }
            DbEngine.SqlCommand("DELETE FROM DiveScores WHERE Dive IN (SELECT Id FROM Dives WHERE Contest = " + contestId + ");");
            DbEngine.SqlCommand("UPDATE Dives SET Date = '' WHERE Contest = " + contestId + ";");
            DbEngine.SqlCommand("UPDATE Contests SET Status = 'Unstarted' WHERE Id = " + contestId + ";");
            return LoadContestFromDb(contestId);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="dive"></param>
        public void DeleteDive(Dive dive)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Deleting dive " + dive.Diver.IdNumber + " " + dive.DiveNumber); }
            DbEngine.SqlCommand("DELETE FROM DiveScores WHERE Dive = " + dive.DbId + ";");
            DbEngine.SqlCommand("DELETE FROM Dives WHERE Id = " + dive.DbId + ";");
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="judge"></param>
        public void DeleteJudge(Judge judge)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Deleting judge " + judge.IdNumber); }
            DbEngine.SqlCommand("DELETE FROM Judges WHERE Id = " + judge.DbId + ";");
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="diver"></param>
        public void DeleteDiver(Diver diver)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Deleting diver " + diver.IdNumber); }
            DbEngine.SqlCommand("DELETE FROM Divers WHERE Id = " + diver.DbId + ";");
            DbEngine.SqlCommand("DELETE FROM DiveScores WHERE Dive IN (SELECT Id FROM Dives WHERE Diver = " + diver.DbId + ");");
            DbEngine.SqlCommand("DELETE FROM Dives WHERE Diver = " + diver.DbId + ";");
        }


        /// <summary>
        /// Deletes a contest from the database, including associated dives and judges.
        /// </summary>
        /// <param name="contest">Contest to delete, gets nulled in the function</param>
        public void DeleteContest(ref Contest contest)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Deleting contest " + contest.Name + " " + contest.Date); }
            DbEngine.SqlCommand("DELETE FROM Judges WHERE Contest = " + contest.DbId + ";");
            DbEngine.SqlCommand("DELETE FROM DiveScores WHERE Dive IN (SELECT Id FROM Dives WHERE Contest = " + contest.DbId + ");");
            DbEngine.SqlCommand("DELETE FROM Dives WHERE Contest = " + contest.DbId + ";");
            DbEngine.SqlCommand("DELETE FROM Contests WHERE Id = " + contest.DbId + ";");
            contest = null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idNumber"></param>
        public Judge LoadJudge(string idNumber)
        {
            var ds =
                DbEngine.SqlQuery(
                    "SELECT Judges.Id, Persons.Id as PersonId, JudgeNumber, IdNumber, FirstName, LastName, Gender, Club, Email, Address, IdString " +
                    "FROM Judges INNER JOIN Persons ON JudgeId=Persons.Id WHERE Person.IdNumber = " + idNumber + ";");

            if (Log.IsDebugEnabled)
            {
                Log.Debug("Loading jugde " + idNumber);
            }
            try
            {
                return new Judge
                {
                    PersonDbId = Convert.ToInt32(ds.Rows[0]["PersonId"]),
                    DbId = Convert.ToInt32(ds.Rows[0]["Id"]),
                    IdNumber = (string)ds.Rows[0]["IdNumber"],
                    FirstName = (string)ds.Rows[0]["FirstName"],
                    LastName = (string)ds.Rows[0]["LastName"],
                    Gender = (Gender)Enum.Parse(typeof(Gender), (string)ds.Rows[0]["Gender"]),
                    Club = (string)ds.Rows[0]["Club"],
                    Email = (string)ds.Rows[0]["Email"],
                    Address = (string)ds.Rows[0]["Address"],
                    IdString = (string)ds.Rows[0]["IdString"],
                    JudgeNumber = Convert.ToInt32(ds.Rows[0]["JudgeNumber"]),
                };
            }
            catch (Exception e)
            {
                if (Log.IsWarnEnabled) { Log.Warn(e); }
                throw;
            }
        }


        public void SaveContestDataToDb(Contest contest)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Saving info about contest " + contest.Name + " " + contest.Date); }
            DbEngine.SqlCommand("REPLACE INTO Contests (Id,Name,Date,Club,City,Country,NumJudges,Status) VALUES (" +
                                    (contest.DbId == null ? "NULL": contest.DbId.ToString()) + ",'" + 
                                    contest.Name + "','" +
                                    contest.Date + "','" +
                                    contest.Club + "','" +
                                    contest.City + "','" +
                                    contest.Country + "','" +
                                    contest.JudgeList.NumberOfJudges + "','" +
                                    contest.Status + "');");

            if (contest.DbId == null)
            {
                contest.DbId =
                        Convert.ToInt32(DbEngine.SqlQuery("SELECT ID FROM Contests " +
                                        "WHERE Name = '" + contest.Name +
                                        "' AND Date = '" + contest.Date +
                                        "' AND Club = '" + contest.Club + "';").Rows[0]["Id"]); 
            }
        }

        /// <summary>
        /// Saves a given object to the appropriate table in the database.
        /// Valid objects are Persons and Contests
        /// Inserts new objects or updates existing ones going by the PersonDbId.
        /// </summary>
        /// <param name="contest">Contest to save to the database.</param>
        public void SaveCompleteContestToDb(Contest contest)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Saving complete contest " + contest.Name + " " + contest.Date); }
            SaveContestDataToDb(contest);

            if (Log.IsDebugEnabled) { Log.Debug("Saving all judges for contest " + contest.Name + " " + contest.Date); }
            foreach (var judge in contest.JudgeList)
            {
                _saveJudgeToDb(judge, contest.DbId);
            }

            var savedDivers = new List<int?>();

            if (Log.IsDebugEnabled) { Log.Debug("Saving DiveQueue for contest " + contest.Name + " " + contest.Date); }
            foreach (var dive in contest.DiveQueue)
            {
                SaveDiveToDb(dive, contest.DbId, savedDivers);
            }

            if (Log.IsDebugEnabled) { Log.Debug("Saving DiveHistory for contest " + contest.Name + " " + contest.Date); }
            foreach (var dive in contest.DiveHistory)
            {
                SaveDiveToDb(dive, contest.DbId, savedDivers);
            }
        }


        /// <summary>
        /// Saves a DiveScore to the Db
        /// </summary>
        /// <param name="score">Score to save</param>
        /// <param name="dive">Dive that the score is associated with</param>
        public void SaveDiveScoreToDb(DiveScore score, Dive dive)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Saving DiveScore " + dive.DiveNumber + " " + score.Judge.IdString + " " + score.Score); }
            DbEngine.SqlCommand("REPLACE INTO DiveScores (Dive, Judge, Score) VALUES(" +
                                    dive.DbId + "," +
                                    score.Judge.DbId + "," +
                                    String.Format(CultureInfo.InvariantCulture,"{0:N}", score.Score) + ");");
        }


        /// <summary>
        /// Loads the contest with the given Id from the database 
        /// </summary>
        /// <param name="contestId"></param>
        /// <returns>A contest comlete with dives and judges</returns>
        public Contest LoadContestFromDb(int? contestId)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Loading contest with id " + contestId); }
            var contestRow = DbEngine.SqlQuery("SELECT * FROM Contests WHERE Id = " + contestId + ";").Rows[0];

            Contest contest;
            try
            {
                contest = new Contest
                    {
                        Name = (string)contestRow["Name"],
                        Date = (string)contestRow["Date"],
                        Club = (string)contestRow["Club"],
                        JudgeList = new JudgeList(Convert.ToInt32(contestRow["NumJudges"])),
                        DiveQueue = new DiveList(),
                        DiveHistory = new DiveList(),
                        City = (string)contestRow["City"],
                        Country = (string)contestRow["Country"],
                        Status = (ContestStatus)Enum.Parse(typeof(ContestStatus), (string)contestRow["Status"]),
                        DbId = Convert.ToInt32(contestRow["Id"])
                    };
            }
            catch (Exception e)
            {
                if (Log.IsWarnEnabled) { Log.Warn(e); }
                throw;
            }

            _loadJudges(contest);

            var loadedDivers = new Dictionary<int, Diver>();
            _loadDivesFromDb(contest, loadedDivers);

            return contest;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public ContestList LoadContestList(bool onlyUpcoming, bool onlyUnstarted)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Loading contestlist"); }

            var query = "SELECT * FROM Contests;";

            if (onlyUpcoming)
            {
                query = query.Insert(query.Length - 1, " WHERE Date >= '" + DateTime.Now.Date.ToShortDateString() + "'");
                if (onlyUnstarted)
                    query = query.Insert(query.Length - 1, " AND Status = 'Unstarted'");
            }
            else if (onlyUnstarted)
                query = query.Insert(query.Length - 1, " WHERE Status = 'Unstarted'");
            var contestTable = DbEngine.SqlQuery(query);

            var contestList = new ContestList();

            try
            {
                foreach (DataRow contestRow in contestTable.Rows)
                {
                    contestList.Add(new Contest
                    {
                        Name = (string)contestRow["Name"],
                        Date = (string)contestRow["Date"],
                        Club = (string)contestRow["Club"],
                        JudgeList = null,
                        DiveQueue = null,
                        DiveHistory = null,
                        City = (string)contestRow["City"],
                        Country = (string)contestRow["Country"],
                        Status = (ContestStatus)Enum.Parse(typeof(ContestStatus), (string)contestRow["Status"]),
                        DbId = Convert.ToInt32(contestRow["Id"])
                    });
                }
            }
            catch (Exception e)
            {
                if (Log.IsWarnEnabled) { Log.Warn(e); }
                throw;
            }
            return contestList;
        }


        /// <summary>
        /// Saves a DiveList to the database.
        /// </summary>
        /// <param name="dive">Dive object to save to the database.</param>
        /// <param name="contestId">The PersonDbId of the contest that the Dive is associated with.</param>
        /// <param name="savedDivers">A list that holds all the divers that have been loaded in by the function</param>
        public void SaveDiveToDb(Dive dive, int? contestId, List<int?> savedDivers = null)
        {
            if (savedDivers == null)
            {
                _saveDiverToDb(dive.Diver, contestId); 
            }
            else if (!savedDivers.Contains(dive.Diver.PersonDbId))
            {
                _saveDiverToDb(dive.Diver, contestId);
                savedDivers.Add(dive.Diver.PersonDbId);
            }

            if (Log.IsDebugEnabled) { Log.Debug("Saving dive :" + dive.DiveNumber + " " + dive.Diver.IdNumber); }
            DbEngine.SqlCommand("REPLACE INTO Dives (Id,Diver,Contest,DiveCode,Difficulty,DiveNumber,DiveHeight,QueueOrder,Date) VALUES(" +
                                    (dive.DbId == null ? "NULL" : dive.DbId.ToString()) + "," +
                                    dive.Diver.DbId + "," +
                                    contestId + ",'" +
                                    dive.DiveCode + "'," +
                                    String.Format(CultureInfo.InvariantCulture,"{0:N}", dive.Difficulty) + "," +
                                    dive.DiveNumber + "," +
                                    dive.DiveHeight + "," +
                                    dive.QueueOrderNumber + "," +
                                    (dive.Date != null ? "'" + dive.Date + "'" : "NULL") + ");");

            if (dive.DbId == null)
            {
                dive.DbId =
                        Convert.ToInt32(DbEngine.SqlQuery("SELECT Id FROM Dives " +
                                        "WHERE Diver = " + dive.Diver.DbId +
                                        " AND DiveNumber = " + dive.DiveNumber +
                                        " AND Contest = " + contestId + ";").Rows[0]["Id"]); 
            }

            foreach (var score in dive.DiveScoreList)
            {
                SaveDiveScoreToDb(score, dive);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public PersonList LoadPersonListFromDb()
        {
            if (Log.IsDebugEnabled) { Log.Debug("Loading personlist"); }
            var ds = DbEngine.SqlQuery("SELECT * FROM Persons;");
            var list = new PersonList();
            try
            {
                foreach (DataRow person in ds.Rows)
                {
                    list.Add(new Person((string)person["IdNumber"], (string)person["FirstName"],
                        (string)person["LastName"], (Gender)Enum.Parse(typeof(Gender), (string)person["Gender"]),
                        (string)person["Address"], (string)person["Club"], (string)person["Email"]) { PersonDbId = Convert.ToInt32(person["Id"]) });
                }
            }
            catch (Exception e)
            {
                if (Log.IsWarnEnabled) { Log.Warn(e); }
                throw;
            }
            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idNumber"></param>
        /// <returns></returns>
        public Person LoadPerson(string idNumber)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Loading person: " + idNumber); }
            var ds = DbEngine.SqlQuery("SELECT * FROM Persons WHERE IdNumber = '" + idNumber + "';");
            if (ds.Rows.Count == 0)
                return null;
            return new Person
            {
                FirstName = (string) ds.Rows[0]["FirstName"],
                IdNumber = (string) ds.Rows[0]["IdNumber"],
                LastName = (string) ds.Rows[0]["LastName"],
                Address = (string) ds.Rows[0]["Address"],
                Club = (string) ds.Rows[0]["Club"],
                Email = (string) ds.Rows[0]["Email"],
                Gender = (Gender) Enum.Parse(typeof (Gender), (string) ds.Rows[0]["Gender"]),
                PersonDbId = Convert.ToInt32(ds.Rows[0]["Id"])
            };
        }
        #endregion

        #region PrivateMethods

        /// <summary>
        /// Save a JudgeList to the database.
        /// </summary>
        /// <param name="judge">Judge object to save.</param>
        /// <param name="contestId">DbId of the contest that this judgelist is associated with.</param>
        private void _saveJudgeToDb(Judge judge, int? contestId)
        {
            _savePersonToDb(judge);

            if (Log.IsDebugEnabled) { Log.Debug("Saving judge :" + judge.IdString); }
            DbEngine.SqlCommand("REPLACE INTO Judges (Id,IdString,JudgeId,Contest,JudgeNumber) VALUES (" +
                                (judge.DbId == null ? "NULL" : judge.DbId.ToString()) + ",'" +
                                judge.IdString + "','" +
                                judge.PersonDbId + "','" +
                                contestId + "'," + judge.JudgeNumber + ");");

            if (judge.DbId == null)
            {
                judge.DbId =
                        Convert.ToInt32(DbEngine.SqlQuery("SELECT Id FROM Judges " +
                                        "WHERE Contest = " + contestId +
                                        " AND JudgeId = " + judge.PersonDbId + ";").Rows[0]["Id"]); 
            }
        }


        /// <summary>
        /// Saves a Person object to the database
        /// </summary>
        /// <param name="diver">Person object to save to the database.</param>
        /// <param name="contestId">DbID of the associated contest</param>
        private void _saveDiverToDb(Diver diver, int? contestId)
        {
            _savePersonToDb(diver);
            if (Log.IsDebugEnabled) { Log.Debug("Saving diver :" + diver.IdNumber); }
            DbEngine.SqlCommand("REPLACE INTO Divers (Id,Diver,Contest,StartNumber) VALUES(" +
                                (diver.DbId == null ? "NULL" : diver.DbId.ToString()) + "," +
                                diver.PersonDbId + "," +
                                contestId + "," +
                                diver.StartNumber + ");");
            if (diver.DbId == null)
            {
                diver.DbId =
                        Convert.ToInt32(DbEngine.SqlQuery("SELECT Id FROM Divers " +
                                        "WHERE Contest = " + contestId +
                                        " AND Diver = " + diver.PersonDbId + ";").Rows[0]["Id"]); 
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="person"></param>
        public void _savePersonToDb(Person person)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Saving person :" + person.IdNumber); }
            DbEngine.SqlCommand("REPLACE INTO Persons (Id,IdNumber,FirstName,LastName,Club,Email,Address,Gender) VALUES(" +
                                (person.PersonDbId == null ? "NULL" : person.PersonDbId.ToString()) + ",'" +
                                person.IdNumber + "','" +
                                person.FirstName + "','" +
                                person.LastName + "','" +
                                person.Club + "','" +
                                person.Email + "','" +
                                person.Address + "','" +
                                person.Gender + "');");

            if (person.PersonDbId == null)
            {
                person.PersonDbId =
                        Convert.ToInt32(DbEngine.SqlQuery("SELECT Id FROM Persons WHERE IdNumber = '" + person.IdNumber + "';").Rows[0]["Id"]); 
            }
        }

        /// <summary>
        /// Loads all the dives for the contest
        /// </summary>
        /// <param name="contest">contest to load dives for</param>
        /// <param name="loadedDivers">dictionary with all the persons that have been loaded form the db</param>
        private void _loadDivesFromDb(Contest contest, Dictionary<int, Diver> loadedDivers)
        {
            if (Log.IsDebugEnabled) { Log.Debug("Loading dives for contest " + contest.Name + " " + contest.Date); }
            var ds = DbEngine.SqlQuery("SELECT * FROM Dives WHERE Contest = " + contest.DbId +
                                       " ORDER BY QueueOrder;");//(Diver + DiveNumber * (SELECT COUNT(Id) FROM " +
                          //"(SELECT * FROM Dives WHERE Contest = " + contest.DbId + " GROUP BY Diver)));");

            foreach (DataRow diveRow in ds.Rows)
            {
                var diver = _getReferencedDiver(loadedDivers, Convert.ToInt32(diveRow["Diver"]));

                var dive = _createDiveObjectFromDb(diver, diveRow, contest.JudgeList);

                if (dive.DiveScoreList.Count == contest.JudgeList.NumberOfJudges)
                {
                    contest.DiveHistory.Add(dive);
                }
                else
                {
                    contest.DiveQueue.Add(dive);
                }
                
            }
        }

        /// <summary>
        /// Creates a diveRow object from a datarow from the Db
        /// </summary>
        /// <param name="diver">diver to perform the diveRow</param>
        /// <param name="diveRow">datarow with data aout the diveRow</param>
        /// <param name="judgeList">List of judges set for the contest</param>
        /// <returns>Dive object loaded from db</returns>
        private Dive _createDiveObjectFromDb(Diver diver, DataRow diveRow, JudgeList judgeList)
        {
            Dive dive;
            try
            {
                dive = new Dive
                    {
                        DbId = Convert.ToInt32(diveRow["Id"]),
                        Diver = diver,
                        DiveCode = (string)diveRow["DiveCode"],
                        Difficulty = Convert.ToDouble(diveRow["Difficulty"]),
                        DiveNumber = Convert.ToInt32(diveRow["DiveNumber"]),
                        DiveHeight = Convert.ToInt32(diveRow["DiveHeight"]),
                        QueueOrderNumber = Convert.ToInt32(diveRow["QueueOrder"]),
                        DiveScoreList = new DiveScoreList(),
                        Date = diveRow["Date"].ToString()
                    };
            }
            catch (Exception e)
            {
                if (Log.IsWarnEnabled) { Log.Warn(e); }
                throw;
            }

            _loadDiveScores(dive, judgeList);
            return dive;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dive"></param>
        /// <param name="judgeList"></param>
        private void _loadDiveScores(Dive dive, JudgeList judgeList)
        {
            if (Log.IsDebugEnabled)
            {
                Log.Debug("Loading scores for dive:" + dive.Diver.IdNumber + " " + dive.DiveNumber);
            }
            var diveScores =
                DbEngine.SqlQuery(
                    "SELECT Score, Judge, JudgeNumber FROM DiveScores INNER JOIN Judges ON Judge=Judges.Id WHERE Dive = " +
                    dive.DbId + " ORDER BY JudgeNumber;");
            if (judgeList.NumberOfJudges < diveScores.Rows.Count)
            {
                throw new DbHandlerException("Too many scores stored for dive");
            }

            try
            {
                foreach (DataRow score in diveScores.Rows)
                {
                    dive.DiveScoreList.Add(new DiveScore
                    {
                        Score = Convert.ToDouble(score["Score"]),
                        Judge = judgeList.Find(x => x.DbId == Convert.ToInt32(score["Judge"]))
                    });
                }
            }
            catch (Exception e)
            {
                if (Log.IsWarnEnabled) { Log.Warn(e); }
                throw;
            }

            if (dive.DiveScoreList.Count == judgeList.NumberOfJudges)
                dive.CalculateTotalScore();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="contest"></param>
        private void _loadJudges(Contest contest)
        {
            var ds =
                DbEngine.SqlQuery(
                    "SELECT Judges.Id, Persons.Id as PersonId, JudgeNumber, IdNumber, FirstName, LastName, Gender, Club, Email, Address, IdString " +
                    "FROM Judges INNER JOIN Persons ON JudgeId=Persons.Id WHERE Contest = " + contest.DbId + ";");

            if (ds.Rows.Count > contest.JudgeList.NumberOfJudges)
            {
                throw new DbHandlerException("Too many judges stored in database for the contest");
            }

            if (Log.IsDebugEnabled)
            {
                Log.Debug("Loading jugdes for  " + contest.Name + " " + contest.Date);
            }
            try
            {
                foreach (DataRow judge in ds.Rows)
                {
                    contest.JudgeList.Add(new Judge
                    {
                        PersonDbId = Convert.ToInt32(judge["PersonId"]),
                        DbId = Convert.ToInt32(judge["Id"]),
                        IdNumber = (string)judge["IdNumber"],
                        FirstName = (string)judge["FirstName"],
                        LastName = (string)judge["LastName"],
                        Gender = (Gender)Enum.Parse(typeof(Gender), (string)judge["Gender"]),
                        Club = (string)judge["Club"],
                        Email = (string)judge["Email"],
                        Address = (string)judge["Address"],
                        IdString = (string)judge["IdString"],
                        JudgeNumber = Convert.ToInt32(judge["JudgeNumber"]),
                    });
                }
            }
            catch (Exception e)
            {
                if (Log.IsWarnEnabled) { Log.Warn(e); }
                throw;
            }
        }


        /// <summary>
        /// Checks to see if the referenced diver is already loaded in from the DB and loads it in otherwise
        /// </summary>
        /// <param name="loadedDivers">Dictionary with all persons already loaded</param>
        /// <param name="id">PersonDbId of referenced diver</param>
        /// <returns>The referenced diver</returns>
        private Diver _getReferencedDiver(Dictionary<int, Diver> loadedDivers, int id)
        {
            Diver diver;
            if (loadedDivers.TryGetValue(id, out diver))
                return diver;
            var personRow = DbEngine.SqlQuery("SELECT Divers.Id, Divers.StartNumber, Persons.Id as PersonId, IdNumber," +
                            " FirstName, LastName, Gender, Club, Email, Address " +
                            "FROM Divers INNER JOIN Persons ON Diver=Persons.Id WHERE Divers.Id = " + id + ";").Rows[0];

            try
            {
                diver = new Diver
                    {
                        PersonDbId = (Convert.ToInt32(personRow["PersonId"])),
                        IdNumber = (string)personRow["IdNumber"],
                        FirstName = (string)personRow["FirstName"],
                        LastName = (string)personRow["LastName"],
                        Gender = (Gender)Enum.Parse(typeof(Gender), (string)personRow["Gender"]),
                        Address = (string)personRow["Address"],
                        Club = (string)personRow["Club"],
                        Email = (string)personRow["Email"],
                        DbId = Convert.ToInt32(personRow["Id"]),
                        StartNumber = Convert.ToInt32(personRow["StartNumber"])
                    };
            }
            catch (Exception e)
            {
                if (Log.IsWarnEnabled) { Log.Warn(e); }
                throw;
            }

            if (Log.IsDebugEnabled) { Log.Debug("Getting diver " + diver.IdNumber ); }
            loadedDivers[id] = diver;
            return diver;
        }

        #endregion
    }
}
